package zhuzx.solution.ir;

import java.util.List;
import java.util.Map;

import org.zhuzx.util.GeneralUtil;
import org.zhuzx.util.JdbcUtil;
import org.zhuzx.util.ListTextDataUtil;
import org.zhuzx.util.MicroUtil;

import zhuzx.generator.GuiUtil;
import zhuzx.toolkit.DataReplicator;

public class IrCompanyBulkProcessor {
	
	/** 天数偏移量，用以指定处理哪天的数据。 */
	private static final int DAYS_OFFSET = 0;
	
	/**
	 * 是否更新所有org_number有误的打卡记录，不管其历史原因是什么（有可能确实是组织变更，比如新成立的子公司）。
	 * 若声明为false，则只更新打到【华新水泥股份】的，这种可以确定是完全没定位到组织。
	 */
	private static final boolean IS_UPDATE_ALL_WRONG_ORG_CLOCK_REC = false;

	public static void main(String[] args) {
		String org = "华新水泥技术管理(武汉)有限公司";
		solveOrgProblematicUserByCompany(org);
	}
	
	/**
	 * 批量解决该公司的所有组织异常用户，及其打卡记录的问题。
	 * @params org 公司全称
	 */
	static void solveOrgProblematicUserByCompany(String org) {
		//先取得精确的公司身份
		String orgSql = "select org_type, org_no from OA.IR_ORG_MAPPING t "
				+ "where t.org_name = '%s' or t.org_name = '%s'";
		orgSql = String.format(orgSql, org, IrDataHelper.convertBrackets(org));
		Map<String,String> orgInfo = IrDataHelper.queryUnique("查mapping表中组织", orgSql, org);
		String orgNo = orgInfo.get("ORG_NO");
		String orgKeyName = "ORG_NUMBER" + orgInfo.get("ORG_TYPE");
		String orgClause = String.format("\nand %s = '%s'", orgKeyName, orgNo);
		//从address表取该公司下所有的手机号
		String sql = "select phone_number from OA.IR_ADDRESS_BOOK "
				+ "where 1=1" + orgClause;
		List<Map<String,String>> list = JdbcUtil.executeQuery(sql);
		String allPhonesOfOrg = JdbcUtil.createInClauseValueStr(list, "PHONE_NUMBER");
		findWrongOperation(orgClause, allPhonesOfOrg);
		//从clock表取当天打了卡但是打错组织的手机号
		String negatedOrgClause = String.format("\nand (%s != '%s' or %s is null)",
				orgKeyName, orgNo, orgKeyName);
		sql = "select distinct name, phone_number, company_name from OA.IR_CLOCK_IN_INFO "
				+ "where phone_number in %s" + negatedOrgClause;
		sql = String.format(sql, allPhonesOfOrg);
		sql += IrDataHelper.createTimeExp("create_date", DAYS_OFFSET);
		list = IrDataHelper.query("查本部门已打卡但组织定位异常的打卡记录", sql, null);
		if (list.size() == 0) {
			return;
		}
		//把这些手机号对应的组织全称拿到，再按组织分批处理，一次处理一个组织+下面所有用户的打卡记录。
		sql = "phone_number in " + JdbcUtil.createInClauseValueStr(list, "PHONE_NUMBER");
		solveOrgProblematicUser(sql);
	}
	
	private static void findWrongOperation(String orgClause, String allPhonesOfOrg) {
		String sql = "select distinct t1.user_id, t1.name, t1.phone_number, t1.clock_in_date,"
				+ "t1.clock_in_id\n" +
				"from OA.IR_ADDRESS_BOOK t1 left join OA.IR_CLOCK_IN_INFO t2\n" + 
				"on t1.phone_number = t2.phone_number" + 
				IrDataHelper.createTimeExp("t2.create_date", DAYS_OFFSET) + "\n" +
				"where t2.id is null" + orgClause.replace("and ", "and t1.");
		List<Map<String,String>> list = IrDataHelper.query(
				"查看无本部打卡记录的人员（包括各种正常异常情况，如尚未打卡、打错组织等）", sql, null);
		//排查是否有单条打卡记录修改手机号，导致更新了多个address记录的打卡状态的情况。
		sql += "\nand t1.clock_in_date = '" + GeneralUtil.getDate() + "'";
		list = IrDataHelper.query("查看异常状态（无打卡记录但是已更新状态）人员", sql, null);
		if (list.size() > 0) {
			sql = "select user_id, name, phone_number, create_date, update_date "
					+ "from OA.IR_CLOCK_IN_INFO\n where id in " +
					JdbcUtil.createInClauseValueStr(list, "CLOCK_IN_ID");
			IrDataHelper.query("查看异常状态（无打卡记录但是已更新状态）人员的最新打卡记录", sql, null);
		}
		//排查是否有外部的手机号打到了本组织，比如错误手机号多打了一次，或者是别的部门成员选错到本组织。
		sql = "select user_id, name, phone_number, create_date, update_date\n"
				+ "from OA.IR_CLOCK_IN_INFO where phone_number not in " + allPhonesOfOrg
				+ IrDataHelper.createTimeExp("create_date", DAYS_OFFSET) + orgClause;
		IrDataHelper.query("查看非本组织在册手机号却打到了本部门的冗余打卡记录", sql, null);
	}
	
	/**
	 * 处理sql查出来的所有组织异常的用户，以组织为单位处理，插入新组织，修正打卡记录。
	 * @param sqlClause 查address表中的异常组织的筛选条件
	 */
	static void solveOrgProblematicUser(String sqlClause) {
		String sql = "select distinct qywx_org_name, org_number2, org_number3, org_number4\n"
				+ "from OA.IR_ADDRESS_BOOK where " + sqlClause;
		List<Map<String,String>> list = IrDataHelper.query("查需要处理的qywx_org_name", sql, null);
		//防脏数据
		int prevCount = list.size();
		ListTextDataUtil.excludeDuplicateRecord(list, "QYWX_ORG_NAME");
		if (prevCount != list.size()) {
			throw new RuntimeException("同一个qywx_org_name有不一致的org_number");
		}
		list.forEach(map -> {
			System.out.println(insertOrgAndUpdateClockInfo(map.get("QYWX_ORG_NAME"),
					map.get("ORG_NUMBER2"), map.get("ORG_NUMBER3"), map.get("ORG_NUMBER4")));
		});
	}
	
	/**
	 * 插入这个新组织到org表，并修正这个新组织下的所有用户的打卡记录的org_number，使之可以被其公司正常统计。
	 */
	private static String insertOrgAndUpdateClockInfo(String fullOrgName,
			String orgNo2, String orgNo3, String orgNo4) {
		boolean isInserted = IrOrgProcessor.insertOrg(fullOrgName, orgNo2, orgNo3, orgNo4);
		String[][] array = {
				{"ORG_NUMBER2", orgNo2},
				{"ORG_NUMBER3", orgNo3},
				{"ORG_NUMBER4", orgNo4},
				{"update_user", "zhuzexiang"}
		};
		Map<String,String> params = GeneralUtil.createMapping(array);
		List<String> orgNameList = IrOrgProcessor.get234OrgNames(params);
		//set子句再加一个字段
		params.put("company_name", orgNameList.get(orgNameList.size() - 1));
		//按这个组织全称下面的用户的手机号查找，处理所有的打卡记录中的org_number。
		String sql = "select phone_number from OA.IR_ADDRESS_BOOK where qywx_org_name = '"
				+ fullOrgName + "'";
		String whereClause = " where phone_number in " +
				JdbcUtil.createInClauseValueStr(JdbcUtil.executeQuery(sql), "PHONE_NUMBER");
		int orgType = orgNameList.size() + 1;
		String orgKeyName = "ORG_NUMBER" + orgType;
		if (IS_UPDATE_ALL_WRONG_ORG_CLOCK_REC) {//所有末级部门不匹配的都会被更新
			//注意，只要用非等于就不要忘记null值。
			whereClause += String.format("\nand (%s != '%s' or %s is null)",
					orgKeyName, params.get(orgKeyName), orgKeyName);
		} else {//为保险起见，只处理打到华新水泥股份的记录。
			whereClause += "\nand org_number2 is null";
		}
		String querySql = "select name, org_number2, org_number3,\n"
				+ "org_number4, company_name, update_date, id, update_user\n"
				+ "from OA.IR_CLOCK_IN_INFO" + whereClause;
		System.out.println("查出需要修正的打卡记录：\n" + querySql);
		List<Map<String,String>> oldRecs = JdbcUtil.executeQuery(querySql);
		String res = "子部门处理完毕，" + (isInserted ? "已插入org表" : "组织已存在");
		if (oldRecs.size() == 0) {
			return res + "，名下没有需要修正的打卡记录。";
		}
		DataReplicator.exportToSqlFile("select * from OA.IR_CLOCK_IN_INFO"+whereClause, "id", null, null);
		GuiUtil.printf(oldRecs, "待修正的原记录" + oldRecs.size() + "条", false);
		sql = "update OA.IR_CLOCK_IN_INFO set " +
				JdbcUtil.createUpdateSqlPart(params) + whereClause;
		int rows = IrDataHelper.update("更新组织下的手机号的所有组织异常打卡记录", sql, fullOrgName);
		querySql = MicroUtil.subBefore(querySql, " where ") + " where id in " +
				JdbcUtil.createInClauseValueStr(oldRecs, "ID");//必须根据id查才能保证查到
		GuiUtil.printf(JdbcUtil.executeQuery(querySql), "更新后", true);
		GuiUtil.countOnExit("更新的打卡记录总数", rows);
		GuiUtil.countOnExit("更新了打卡记录的组织数", rows);
		return res + "，更新了名下的打卡记录" + rows + "行。";
	}
	
}